﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Loogn.DataMeta
{
    static class Sql
    {
        public static readonly string GetTablesSql = @"
SELECT  t.Name ,
        ISNULL(p.value, '') Description
FROM    sysobjects t
        LEFT JOIN sys.extended_properties p ON t.id = p.major_id
                                               AND p.minor_id = 0
WHERE   t.xtype = 'U'
        AND t.name <> 'dtproperties'
ORDER BY t.name";

        public static readonly string GetColumnsSql = @"
SELECT  c.Name ,
        c.IsNullable ,
        ( CASE WHEN COLUMNPROPERTY(c.id, c.name, 'IsIdentity') = 1 THEN 1
               ELSE 0
          END ) IsIdentity ,
        ( CASE WHEN ( SELECT    COUNT(*)
                      FROM      sysobjects
                      WHERE     ( name IN (
                                  SELECT    name
                                  FROM      sysindexes
                                  WHERE     ( id = c.id )
                                            AND ( indid IN (
                                                  SELECT    indid
                                                  FROM      sysindexkeys
                                                  WHERE     ( id = c.id )
                                                            AND ( colid IN (
                                                              SELECT
                                                              colid
                                                              FROM
                                                              syscolumns
                                                              WHERE
                                                              ( id = c.id )
                                                              AND ( name = c.name ) ) ) ) ) ) )
                                AND ( xtype = 'PK' )
                    ) > 0 THEN 1
               ELSE 0
          END ) IsPrimaryKey ,
        t.name AS SqlDataType ,
        ( CASE t.name
            WHEN 'nvarchar' THEN c.length / 2
            WHEN 'nchar' THEN c.length / 2
            ELSE c.length
          END ) AS Length ,
        ISNULL(p.value, '') Description ,
        ISNULL(m.text, 'null') DefaultValue
FROM    syscolumns c
        LEFT JOIN systypes t ON c.xtype = t.xtype
                                AND t.name <> 'sysname'
        LEFT JOIN sys.extended_properties p ON c.colid = p.minor_id
                                               AND p.major_id = c.id
        LEFT JOIN syscomments m ON c.cdefault = m.id
WHERE   c.id = OBJECT_ID(@tableName)
ORDER BY colorder ASC
";
    }
}
